package com.persistence.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.model.Color;
import com.model.Estructura;
import com.model.Personal;
import com.model.Modelo;
import com.persistence.dao.DAOException;
import com.persistence.dao.DAOManagerMain;
import com.persistence.dao.interfaces.DAOEstructura;

public class MySQLDAOEstructura implements DAOEstructura {
	
	private Estructura estructura = null;
	
	
	public MySQLDAOEstructura() {
	}

	public int insert(Estructura estructura) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection.prepareStatement("insert into Estructura ("
					+ "Id_Modelo, fecha_Emision, fecha_Aprobacion "
					+ "aprobadoPor, Id_Estructura, Id_Color "
					+ ") " + "values (?, ?, ?, ?, ?, ?) ");

			stmt.setLong(1, estructura.getNroModelo().getNroModelo());
			stmt.setString(2, estructura.getFechaemision());
			stmt.setString(3, estructura.getFechaaprobacion());
			stmt.setLong(4, estructura.getAprobadopor().getId_Personal());
			stmt.setLong(5, estructura.getNroEstructura());
			stmt.setLong(6, estructura.getNroColor().getNroColor());
			stmt.executeUpdate();
			
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);

		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public int update(Estructura estructura) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("update Estructura " 
							+ "set fecha_Emision = ?, fecha_Aprobacion = ?, aprobadoPor = ?,"
							+ "Id_Color = ?, Id_Modelo = ?"
							+ " where id_Estructura = ? ");

			stmt.setString(1, estructura.getFechaemision());
         stmt.setString(2, estructura.getFechaaprobacion());
         stmt.setLong(3, estructura.getAprobadopor().getId_Personal());
         stmt.setLong(4, estructura.getNroEstructura());
         stmt.setLong(5, estructura.getNroColor().getNroColor());

			stmt.executeUpdate();
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public int eliminate(Estructura estructura) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("delete from Estructura where id_Estructura = ? ");
			stmt.setLong(1, estructura.getNroEstructura());

			stmt.executeUpdate();
			return 1;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public ArrayList<Estructura> select() throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		ArrayList<Estructura> estructuras = new ArrayList<Estructura>();

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection.prepareStatement("select E.*, M.*, C.*, P.* from"
			+ " Estructura E, Modelo T, Color C, Personal P "
         + "where E.Id_Modelo=M.Id_Modelo and E.Id_Color=M.Id_Color"
         + " and E.aprobadoPor=P.Id_Personal");
			rs = stmt.executeQuery();

			while (rs.next()) {
				Estructura estructura = new Estructura();
				estructura.setNroEstructura(rs.getInt("Id_Estructura"));
				estructura.setFechaemision(rs.getString("fecha_Emision"));
				estructura.setFechaemision(rs.getString("fecha_Aprobacion"));
				estructura.setAprobadopor(new Personal());
				estructura.getAprobadopor().setId_Personal(rs.getInt("aprobadoPor"));
				estructura.setNroColor(new Color());
				estructura.getNroColor().setNroColor(rs.getInt("Id_Color"));
				estructura.setNroModelo(new Modelo());
            estructura.getNroModelo().setNroModelo(rs.getInt("Id_Modelo"));
            
				estructuras.add(estructura);		
			}

			return estructuras;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	public Estructura findById(int id) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		estructura = new Estructura();

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("select E.*, M.*, C.*, P.* from"
			         + " Estructura E, Modelo T, Color C, Personal P "
			         + "where E.Id_Modelo=M.Id_Modelo and E.Id_Color=M.Id_Color"
			         + " and E.aprobadoPor=P.Id_Personal "   
					+		"where Id_Estructura = ?");
			stmt.setLong(1, id);
			rs = stmt.executeQuery();

			if (rs.next()) {
			   Estructura estructura = new Estructura();
            estructura.setNroEstructura(rs.getInt("Id_Estructura"));
            estructura.setFechaemision(rs.getString("fecha_Emision"));
            estructura.setFechaemision(rs.getString("fecha_Aprobacion"));
            estructura.setAprobadopor(new Personal());
            estructura.getAprobadopor().setId_Personal(rs.getInt("aprobadoPor"));
            estructura.setNroColor(new Color());
            estructura.getNroColor().setNroColor(rs.getInt("Id_Color"));
            estructura.setNroModelo(new Modelo());
            estructura.getNroModelo().setNroModelo(rs.getInt("Id_Modelo"));
			}

			return estructura;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}

	@Override
	public boolean existInstance(Estructura estructura) throws DAOException {
		Connection connection = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		boolean result = false;

		try {
			connection = DAOManagerMain.manager().getConnection();
			stmt = connection
					.prepareStatement("select * from Estructura where UCASE(Nombre)");

//			stmt.setString(1, estructura.getNombre().toUpperCase());
//			stmt.setString(2, estructura.getApellido().toUpperCase());
			rs = stmt.executeQuery();

			if (rs.next()) {
				result = true;
			}

			return result;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			DAOManagerMain.manager().closeAll(connection, stmt, rs);
		}
	}
}
